﻿Imports ISNet.WebUI.WebTreeView
Imports DataDynamics.SpreadBuilder
Imports Microsoft.Office.Interop
Imports System.Data
Imports System.Collections
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Globalization
Imports log4net


Partial Class PivotReport
    Inherits System.Web.UI.Page

    Public ScriptText As String = ""
    Private Shared logger As ILog = LogManager.GetLogger("PivotReport")

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        Label1.Text = "ระบบจัดทำงบประมาณ >> รายงาน >> Pivot"
        If Session("users") Is Nothing Then
            Response.Redirect("LoginForm.aspx")
        End If

        If IsPostBack = False Then
            Try
                Dim adap As New DBdataTableAdapters.AppConfigsTableAdapter
                Dim dt As New DBdata.AppConfigsDataTable
                dt = adap.GetData
                For Each item As DBdata.AppConfigsRow In dt
                    Select Case item.Name
                        Case "ACTIVE_PLAN_YEAR"
                            PlanYearsTxt.Text = item.Value
                    End Select
                Next
            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)
            End Try
        End If


    End Sub




    Protected Sub toReport_Click(sender As Object, e As ImageClickEventArgs) Handles toReport.Click
        If Val(PlanYearsTxt.Text) = 0 Then
            ScriptText = "<script language='javascript'>alert('กรุณาระบุปีงบประมาณ');</script>"
            Exit Sub
        End If

        Dim appXL As Microsoft.Office.Interop.Excel.Application
        Dim wbXl As Excel.Workbook
        Dim shXL As Excel.Worksheet
        Dim raXL As Excel.Range
        ' Start Excel and get Application object.
        appXL = CreateObject("Excel.Application")
        appXL.Visible = True
        ' Add a new workbook.
        wbXl = appXL.Workbooks.Add
        shXL = wbXl.ActiveSheet
        ' Add table headers going cell by cell.

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("BudDbConnectionString").ConnectionString
        Dim queryString As String = "Select * from FundCenters"
        Dim paramValue As Integer = 5

        Using connection As New SqlConnection(connectionString)
            Dim command As New SqlCommand(queryString, connection)
            'command.Parameters.AddWithValue("@pricePoint", paramValue)
            Try
                connection.Open()
                Dim rowCount As Integer = 0
                Dim dataReader As SqlDataReader = command.ExecuteReader()
                Do While dataReader.Read()
                    rowCount = rowCount + 1
                    shXL.Cells(rowCount, 1).Value = dataReader(0)
                    shXL.Cells(rowCount, 2).Value = dataReader(1)
                    shXL.Cells(rowCount, 3).Value = dataReader(2)
                    shXL.Cells(rowCount, 4).Value = dataReader(3)
                Loop
                dataReader.Close()

            Catch ex As Exception
                logger.Error(ex.Message)
                logger.Error(ex.StackTrace)
                Console.WriteLine(ex.Message)
            End Try
            Console.ReadLine()
        End Using
        ' Make sure Excel is visible and give the user control
        ' of Excel's lifetime.
        appXL.Visible = True
        appXL.UserControl = True
        wbXl.SaveCopyAs("d:\Temp\test.xls")

        ' Release object references.
        raXL = Nothing
        shXL = Nothing
        wbXl = Nothing
        appXL.Quit()
        appXL = Nothing
        Exit Sub
Err_Handler:
        MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
    End Sub

    Private Sub SqlCommand(p1 As Object)
        Throw New NotImplementedException
    End Sub

    Private Function cmd() As SqlClient.SqlCommand
        Throw New NotImplementedException
    End Function

    Private Sub SqlDataAdapter(p1 As Object)
        Throw New NotImplementedException
    End Sub

    Private Function connectionString() As String
        Throw New NotImplementedException
    End Function

End Class
